﻿'Bram Debouvere
Imports System.Collections.Generic
Imports System.Data.OleDb


Public Class Person
    Private _id, _voornaam, _naam, _aanspreking, _adreslijn1, _adreslijn2, _postcode, _plaats, _land, _telefoon, _mobiel, _email As String

    Public Sub New(ByVal id As String, ByVal voornaam As String, ByVal naam As String, ByVal aanspreking As String, ByVal adreslijn1 As String, ByVal adreslijn2 As String, ByVal postcode As String, ByVal plaats As String, ByVal land As String, ByVal telefoon As String, ByVal mobiel As String, ByVal email As String)
        _id = id
        _voornaam = voornaam
        _naam = naam
        _aanspreking = aanspreking
        _adreslijn1 = adreslijn1
        _adreslijn2 = adreslijn2
        _postcode = postcode
        _plaats = plaats
        _land = land
        _telefoon = telefoon
        _mobiel = mobiel
        _email = email
    End Sub


    Public Property Id As String
        Get
            Return _id
        End Get
        Set(ByVal Value As String)
            _id = Value
        End Set
    End Property

    Public Property Voornaam As String
        Get
            Return _voornaam
        End Get
        Set(ByVal Value As String)
            _voornaam = Value
        End Set
    End Property

    Public Property Naam As String
        Get
            Return _naam
        End Get
        Set(ByVal Value As String)
            _naam = Value
        End Set
    End Property

    Public Property Aanspreking As String
        Get
            Return _aanspreking
        End Get
        Set(ByVal Value As String)
            _aanspreking = Value
        End Set
    End Property

    Public Property Adreslijn1 As String
        Get
            Return _adreslijn1
        End Get
        Set(ByVal Value As String)
            _adreslijn1 = Value
        End Set
    End Property

    Public Property Adreslijn2 As String
        Get
            Return _adreslijn2
        End Get
        Set(ByVal Value As String)
            _adreslijn2 = Value
        End Set
    End Property

    Public Property Postcode As String
        Get
            Return _postcode
        End Get
        Set(ByVal Value As String)
            _postcode = Value
        End Set
    End Property

    Public Property Plaats As String
        Get
            Return _plaats
        End Get
        Set(ByVal Value As String)
            _plaats = Value
        End Set
    End Property

    Public Property Land As String
        Get
            Return _land
        End Get
        Set(ByVal Value As String)
            _land = Value
        End Set
    End Property

    Public Property Telefoon As String
        Get
            Return _telefoon
        End Get
        Set(ByVal Value As String)
            _telefoon = Value
        End Set
    End Property

    Public Property Mobiel As String
        Get
            Return _mobiel
        End Get
        Set(ByVal Value As String)
            _mobiel = Value
        End Set
    End Property

    Public Property Email As String
        Get
            Return _email
        End Get
        Set(ByVal Value As String)
            _email = Value
        End Set
    End Property

    Public Shared Function GetPersonsFromDatabase(ByRef dbh As DatabaseHelper) As List(Of Person)
        Dim list As New List(Of Person)
        Dim reader As OleDbDataReader = dbh.GetReader("SELECT * FROM Persoon ORDER BY PerNaam,PerVoornaam", {})
        Do While reader.Read()
            Dim persoon As New Person(CStr(reader("PerId")), CStr(reader("PerVoornaam")), CStr(reader("PerNaam")), CStr(reader("PerAanspreking")), CStr(reader("PerAdreslijn1")), CStr(reader("PerAdreslijn2")), CStr(reader("PerPostcode")), CStr(reader("PerPlaats")), CStr(reader("PerLand")), CStr(reader("PerTelefoon")), CStr(reader("PerMobiel")), CStr(reader("PerEmail")))
            list.Add(persoon)
        Loop
        reader.Close()
        Return list
    End Function
    'needed in orders
    Public Shared Function GetPersonFromDatabase(ByRef dbh As DatabaseHelper, ByVal perId As Integer) As Person
        Dim reader As OleDbDataReader = dbh.GetReader("SELECT * FROM Persoon  WHERE PerId = ?", {perId})
        Dim person As Person
        reader.Read()
        person = New Person(CStr(reader("PerId")), CStr(reader("PerVoornaam")), CStr(reader("PerNaam")), CStr(reader("PerAanspreking")), CStr(reader("PerAdreslijn1")), CStr(reader("PerAdreslijn2")), CStr(reader("PerPostcode")), CStr(reader("PerPlaats")), CStr(reader("PerLand")), CStr(reader("PerTelefoon")), CStr(reader("PerMobiel")), CStr(reader("PerEmail")))
        reader.Close()
        Return person
    End Function
    Public Shared Sub AddPersonToDatabase(ByVal Person As Person, ByRef dbh As DatabaseHelper)
        dbh.ExecuteSQL("INSERT INTO Persoon VALUES (?,?,?,?,?,?,?,?,?,?,?)", {Person.Voornaam, Person.Naam, Person.Aanspreking, Person.Adreslijn1, Person.Adreslijn2, Person.Postcode, Person.Plaats, Person.Land, Person.Telefoon, Person.Mobiel, Person.Email}, True)
    End Sub

    Public Shared Sub UpdatePersonInDatabase(ByVal Person As Person, ByRef dbh As DatabaseHelper)
        dbh.ExecuteSQL("UPDATE Persoon SET PerVoornaam = ?, PerNaam= ?, PerAanspreking = ?, PerAdreslijn1=? , PerAdreslijn2=?, PerPostcode=?, PerPlaats=?, PerLand=?, PerTelefoon=?, PerMobiel=?, PerEmail=? WHERE (PerId = ? )", {Person.Voornaam, Person.Naam, Person.Aanspreking, Person.Adreslijn1, Person.Adreslijn2, Person.Postcode, Person.Plaats, Person.Land, Person.Telefoon, Person.Mobiel, Person.Email, Person.Id}, True)
    End Sub

    Public Shared Sub DeletePersonFromDatabase(ByVal Person As Person, ByRef dbh As DatabaseHelper)
        dbh.ExecuteSQL("DELETE FROM Persoon WHERE PerId=?", {Person.Id}, True)
    End Sub

    Public Shared Function CheckPersonForOrderOrList(ByVal Person As Person, ByRef dbh As DatabaseHelper) As Boolean
        Dim reader As OleDbDataReader = dbh.GetReader("SELECT PerId FROM Persoon,GeschenkLijst,Bestelling WHERE ( PerId = ? And (PerId = BestPerId Or PerId = LijstPerId)) ", {Person.Id})
        Dim found As Boolean
        Do While reader.Read()
            found = True
        Loop
        reader.Close()
        Return found
    End Function

    'Tostring function needed for orders
    Public Overrides Function toString() As String
        Return _aanspreking & " " & _naam & " " & _voornaam
    End Function
End Class
